﻿using FTBS.Models.Entities;
using FTBS.Models.Entities.Concert;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace FTBS.Models.DAO
{
    public class FilmDAO
    {
        private FilmTicketBookingSystemEntities _entities = new FilmTicketBookingSystemEntities();
        public DateTime today = System.DateTime.Today;

        /// <summary>
        /// Load all film
        /// </summary>
        /// <returns>List of Film</returns>
        /// <author>VuongVT</author>
        public List<Film> LoadFilms()
        {
            try
            {
                List<Film> listFilm = new List<Film>();
                var films = from film in _entities.Films
                            where film.IsDeleted == false
                            orderby film.FormatId descending
                            select film;
                if (films != null)
                {
                    listFilm = films.ToList();
                }
                return listFilm;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get list film by TheaterId
        /// </summary>
        /// <param name="theaterId"></param>
        /// <returns>List of Film</returns>
        public List<Film> GetListFilmsByTheaterId(int theaterId)
        {
            try
            {
                List<Film> listFilm = new List<Film>();

                var resultSearch = _entities.Schedules.Where(s => s.TheaterId == theaterId && s.IsDeleted == false)
                                                      .GroupBy(s => s.FilmId)
                                                      .Select(r => new
                                                      {
                                                          FilmId = r.Key
                                                      })
                                                      .Join(_entities.Films, sch => sch.FilmId, f => f.FilmId, (sch, f) => new { sch, f })
                                                      .Select(r => new
                                                      {
                                                          Film = r.f
                                                      });
                foreach (var item in resultSearch)
                {
                    listFilm.Add(item.Film);
                }
                return listFilm;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Load Film By Id
        /// </summary>
        /// <returns>Film</returns>
        /// <author>VuongVT</author>
        public Film LoadFilmsById(int id)
        {
            try
            {
                Film film = new Film();
                film = (from filmEnt in _entities.Films
                        where filmEnt.FilmId == id
                             && filmEnt.IsDeleted == false
                        select filmEnt).FirstOrDefault();
                return film;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Add a film
        /// </summary>
        /// <returns>1: Success -- 0: Fail</returns>
        /// <author>VuongVT</author>
        public int Add(Film film)
        {
            try
            {
                _entities.Films.Add(film);
                int resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        /// <summary>
        /// Edit a Film
        /// </summary>
        /// <returns>1: Success -- 0: Fail</returns>
        /// <author>VuongVT</author>
        public int Edit(Film film)
        {
            try
            {
                _entities.Entry(film).State = EntityState.Modified;
                int resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        /// <summary>
        /// Delete a film
        /// </summary>
        /// <returns>1: Success -- 0: Fail</returns>
        /// <author>VuongVT</author>
        public int Delete(Film film)
        {
            try
            {
                film.IsDeleted = true;
                _entities.Entry(film).State = EntityState.Modified;
                int result = _entities.SaveChanges();
                return result;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        /// <summary>
        /// Get first 7 films by favorite category id
        /// </summary>
        /// <param name="cateId"></param>
        /// <returns>List of 7 films</returns>
        /// <author>VuongVT</author>
        public List<Film> GetFilmsByCateId(int cateId)
        {
            List<Film> films = new List<Film>();
            if (cateId == 0)
            {
                return films;
            }
            else
            {
                try
                {
                    films = (from film in _entities.Films
                             from schedule in _entities.Schedules
                             from showtime in _entities.ShowTimes
                             where film.CategoryId == cateId
                                   && film.IsDeleted == false
                                   && film.FilmId == schedule.FilmId
                                   && showtime.ShowTimeId == schedule.ShowTimeId
                                   && schedule.IsActive == true
                                   && schedule.Date >= today
                             orderby film.FilmId descending
                             select film).Take(7).ToList();
                }
                catch (Exception e)
                {
                    throw e;
                }
                return films;
            }
        }

        ///// <summary>
        ///// get first 7 newest films
        ///// </summary>
        ///// <param name="cateId"></param>
        ///// <returns>list of 7 films</returns>
        ///// <author>DuyHDV</author>
        //public List<Film> GetNewestFilms()
        //{
        //    List<Film> films = new List<Film>();
        //    try
        //    {
        //        films = (
        //                    from film in _entities.Films
        //                    from schedule in _entities.Schedules
        //                    from showtime in _entities.ShowTimes
        //                    where
        //                    film.IsDeleted == false
        //                    && film.FilmId == schedule.FilmId
        //                    && showtime.ShowTimeId == schedule.ShowTimeId
        //                    && schedule.Date >= today
        //                    orderby film.FilmId descending
        //                    select film
        //                ).Take(7).ToList();
        //    }
        //    catch (Exception e)
        //    {
        //        throw e;
        //    }
        //    return films;
        //}

        /// <summary>
        /// Check if film name is existed or not
        /// This function is used when Edit film
        /// </summary>
        /// <param name="film"></param>
        /// <returns>exist: true ; not exist: false</returns>
        /// <author>hungph</author>
        /// <date>3/3/2014</date>
        public bool IsFilmExistedUpdate(Film film)
        {
            try
            {
                var isExist = _entities.Films.Any(filmEnt => filmEnt.Name == film.Name
                                                       && filmEnt.IsDeleted == false
                                                       && filmEnt.FilmId != film.FilmId);
                return isExist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Check if film name is existed or not
        /// This function is used when Edit film
        /// </summary>
        /// <param name="film"></param>
        /// <returns>exist: true ; not exist: false</returns>
        /// <author>hungph</author>
        /// <date>3/3/2014</date>
        public bool IsFilmExistedCreate(Film film)
        {
            try
            {
                var isExist = _entities.Films.Any(filmEnt => filmEnt.Name == film.Name
                                                       && filmEnt.IsDeleted == false);
                return isExist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Film GetFilmById(int id)
        {
            var film = _entities.Films.Find(id);
            return film;
        }

        /// <summary>
        /// Load Film By Id
        /// </summary>
        /// <returns>Film</returns
        /// <author>DuyHDV</author>
        public Film LoadFilmById(int id)
        {
            Film film = new Film();
            try
            {
                film = _entities.Films.Find(id);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return film;
        }

        /// <summary>
        /// Get list of film which customer bought. This function is used to suggest film for customer
        /// </summary>
        /// <param name="userName"></param>
        /// <returns>List of film which customer bought</returns>
        /// <author>VuongVT</author>
        public List<Film> GetListBoughtFilmOfUser(string userName)
        {
            try
            {
                List<Film> listFilmMostBought = new List<Film>();
                List<TicketCart> listTicketCart = new List<TicketCart>();

                var resultQuery = (from ticketCart in _entities.TicketCarts
                                   from ticket in _entities.Tickets
                                   where ticketCart.UserName == userName
                                         && ticketCart.IsPaid == true
                                         && ticketCart.IsDeleted == false
                                         && ticket.CartId == ticketCart.CartId
                                   select ticketCart).ToList();
                listTicketCart = resultQuery.OrderBy(r => r.TotalPrice).ToList();

                foreach (var item in listTicketCart)
                {
                    //Get scheduleId of ticketCart
                    var scheduleId = (from ticket in _entities.Tickets
                                      where ticket.CartId == item.CartId
                                      select ticket.ScheduleId).FirstOrDefault();

                    //Get Schedule by scheduleId
                    var selectFilmId = (from schedule in _entities.Schedules
                                         where schedule.ScheduleId == scheduleId
                                         select schedule.FilmId).FirstOrDefault();

                    //Select categoryId of booked film
                    var selectFilmCategoryId = (from film in _entities.Films
                                                where film.FilmId == selectFilmId
                                                select film.CategoryId).FirstOrDefault();

                    //Select in Schedule all film have booked film categoryid
                    var resultListFilm = from schedule in _entities.Schedules
                                         from film in _entities.Films
                                         where schedule.FilmId == film.FilmId
                                               && schedule.IsDeleted == false
                                               && schedule.IsActive == true
                                               && schedule.IsCreateTicket == true
                                               && film.CategoryId == selectFilmCategoryId
                                               && film.IsDeleted == false
                                         select film;
                    foreach (var film in resultListFilm)
                    {
                        if (!listFilmMostBought.Contains(film))
                        {
                            listFilmMostBought.Add(film);
                        }
                        
                    }
                }
                return listFilmMostBought;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get list film suggest for Customer
        /// </summary>
        /// <param name="listLocalStorage"></param>
        /// <returns>List of Film</returns>
        /// <author>VuongVT</author>
        public List<Film> GetListFilmSuggestionForCustomer(List<LocalStorageConcertEntity> listLocalStorage, string userName)
        {
            try
            {
                List<Film> listFilmSuggest = new List<Film>();
                List<Film> listBoughtFilm = new List<Film>();
                listFilmSuggest = GetListBoughtFilmOfUser(userName);
                //First time, compare which list bought film of customer to suggest film for customer.
                //If list film after compare which list bought film don't have enought 7 film. Continue compare with list films are showing to get
                //enought 7 film
                //for (int i = 0; i < listLocalStorage.Count(); i++)
                //{
                //    int localCategoryId = listLocalStorage[i].CategoryId;
                //    foreach (var film in listBoughtFilm)
                //    {
                //        if (film.CategoryId == localCategoryId)
                //        {
                //            if (!listFilmSuggest.Contains(film))
                //            {
                //                listFilmSuggest.Add(film);
                //            }
                //        }
                //    }
                //}

                //Continue compare with list films are showing to get enought 7 film
                if (listFilmSuggest.Count < 7)
                {
                    for (int i = 0; i < listLocalStorage.Count(); i++)
                    {
                        int localCategoryId = listLocalStorage[i].CategoryId;
                        var resultQuery = (from film in _entities.Films
                                           from schedule in _entities.Schedules
                                           where film.IsDeleted == false
                                           && film.CategoryId == localCategoryId
                                           && schedule.FilmId == film.FilmId
                                           && schedule.IsDeleted == false
                                           && schedule.IsActive == true
                                           && schedule.ShowTime.IsDeleted == false
                                           select film).ToList();
                        if (listFilmSuggest.Count < 7)
                        {
                            bool stop = false;
                            foreach (var item in resultQuery)
                            {
                                if (!listFilmSuggest.Contains(item))
                                {
                                    listFilmSuggest.Add(item);
                                    if (listFilmSuggest.Count >= 7)
                                    {
                                        stop = true;
                                        break;
                                    }
                                }
                            }
                            if (stop)
                            {
                                break;
                            }
                        }
                    }
                }
                return listFilmSuggest;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Get list film suggest for Guest
        /// </summary>
        /// <param name="listLocalStorage"></param>
        /// <returns>List of Film</returns>
        /// <author>VuongVT</author>
        public List<Film> GetListFilmSuggestionForGuest(List<LocalStorageConcertEntity> listLocalStorage)
        {
            try
            {
                List<Film> listFilmSuggest = new List<Film>();
                for (int i = 0; i < listLocalStorage.Count(); i++)
                {
                    int localCategoryId = listLocalStorage[i].CategoryId;

                    var resultQuery = (from film in _entities.Films
                                       from schedule in _entities.Schedules
                                       where film.IsDeleted == false
                                       && film.CategoryId == localCategoryId
                                       && schedule.FilmId == film.FilmId
                                       && schedule.IsDeleted == false
                                       && schedule.IsActive == true
                                       && schedule.ShowTime.IsDeleted == false
                                       select film).ToList();
                    if (listFilmSuggest.Count < 7)
                    {
                        bool stop = false;
                        foreach (var item in resultQuery)
                        {
                            if (!listFilmSuggest.Contains(item))
                            {
                                listFilmSuggest.Add(item);
                                if (listFilmSuggest.Count >= 7)
                                {
                                    stop = true;
                                    break;
                                }
                            }
                        }
                        if (stop)
                        {
                            break;
                        }
                    }

                }
                return listFilmSuggest;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// count exist film with format id 
        /// </summary>
        /// <param name="formatId"></param>
        /// <returns>number of exist films with format id</returns>
        /// <author>hungph</author>
        public int CountNotDeleteFilmByFormatId(int formatId)
        {
            int count = _entities.Films.Count(film => film.IsDeleted == false && film.FormatId == formatId);
            return count;
        }

        /// <summary>
        /// count exist comment by film id 
        /// </summary>
        /// <param name="filmId"></param>
        /// <returns>number of comments by film id</returns>
        /// <author>hungph</author>
        public int CountCommentByFilmId(int filmId)
        {
            int count = _entities.Comments.Count(comment => comment.IsDeleted == false && comment.FilmId == filmId);
            return count;
        }

        /// <summary>
        /// count exist schedules by film id 
        /// </summary>
        /// <param name="filmId"></param>
        /// <returns>number of schedules by film id</returns>
        /// <author>hungph</author>
        public int CountSchedulesByFilmId(int filmId)
        {
            int count = _entities.Schedules.Count(s => s.IsDeleted == false && s.FilmId == filmId);
            return count;
        }

        /// <summary>
        /// Check the FilmId is used in Schedule and have Booked ticket or not
        /// This function is used for user Click to Edit in List Film
        /// </summary>
        /// <param name="showTimeId"></param>
        /// <returns>True: Is Using And Booked ---- False: Using And Booked </returns>
        /// <author>VuongVT</author>
        public bool IsFilmUsingAndHaveBookedTicket(int filmId)
        {
            try
            {
                var isUsedSchedule = false;
                var scheduleId = (from schedule in _entities.Schedules
                                  where schedule.FilmId == filmId
                                        && schedule.IsDeleted == false
                                  select schedule.ScheduleId).FirstOrDefault();
                if (scheduleId != 0)
                {
                    var isBookedTicket = _entities.Tickets.Any(ticket => ticket.ScheduleId == scheduleId
                                                                     && ticket.IsDeleted == false
                                                                     && ticket.Status == true);
                    if (isBookedTicket)
                    {
                        isUsedSchedule = true;
                    }
                }
                return isUsedSchedule;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Check the FilmId is used in Schedule or not
        /// This function is used for user Click to Delete in List Film
        /// </summary>
        /// <param name="showTimeId"></param>
        /// <returns>True: Is Exist ---- False: Not Exist</returns>
        /// <author>VuongVT</author>
        public bool IsFilmUsedForSchedule(int filmId)
        {
            try
            {
                var isUsedForSchedule = _entities.Schedules.Any(schedule => schedule.FilmId == filmId
                                                                            && schedule.IsDeleted == false);
                return isUsedForSchedule;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get all showing films
        /// This function use for showing films in Film/ListShowingFilms view
        /// </summary>
        /// <returns>List of films</returns>                       
        /// <author>DuyHDV</author>    
        public List<Film> GetShowingFilms()
        {
            List<Film> listShowingFilms = new List<Film>();
            try
            {
                var listFilms = (
                    from film in _entities.Films
                    from schedule in _entities.Schedules
                    where
                    film.FilmId == schedule.FilmId &&
                    film.IsDeleted == false &&
                    schedule.Date >= DateTime.Today &&
                    schedule.IsActive == true &&
                    schedule.IsCreateTicket == true &&
                    schedule.IsDeleted == false
                    orderby
                    film.FilmId
                    select
                    film
                    ).ToList();
                if (listFilms.Count() > 0)
                {
                    string film = "";
                    foreach (var f in listFilms)
                    {
                        if (f.Name != film)
                        {
                            listShowingFilms.Add(f);
                            film = f.Name;
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return listShowingFilms;
        }

        /// <summary>
        /// Get all promotional films, which include coming films
        /// This function use for showing films in Film/ListPromotionalFilms view
        /// </summary>
        /// <returns>List of films</returns>                  
        /// <author>DuyHDV</author>
        public List<Film> GetPromotionalFilms()
        {
            List<Film> listPromotionalFilms = new List<Film>();
            try
            {
                var listFilms = (
                    from film in _entities.Films
                    from schedule in _entities.Schedules
                    where
                    film.FilmId == schedule.FilmId &&
                    schedule.Date >= DateTime.Today &&
                    schedule.IsCreateTicket == true &&
                    schedule.IsDeleted == false &&
                    schedule.PromotionId != null
                    orderby
                        film.FilmId
                    select
                    film
                    ).ToList();
                if (listFilms.Count() > 0)
                {
                    string film = "";
                    foreach (var f in listFilms)
                    {
                        if (f.Name != film)
                        {
                            listPromotionalFilms.Add(f);
                            film = f.Name;
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return listPromotionalFilms;
        }

        /// <summary>
        ///  Get coming films
        /// This function use for showing films in Film/ListComingFilms view
        /// </summary>
        /// <returns>List of films</returns>
        /// <author>DuyHDV</author>
        public List<Film> GetComingFilms()
        {
            List<Film> listComingFilms = new List<Film>();
            try
            {
                var listFilms = (
                    from film in _entities.Films
                    from schedule in _entities.Schedules
                    where
                    film.FilmId == schedule.FilmId &&
                    schedule.Date >= DateTime.Today &&
                    schedule.IsActive == false &&
                    schedule.IsDeleted == false
                    orderby
                        film.FilmId
                    select
                    film
                    ).ToList();
                if (listFilms.Count() > 0)
                {
                    string film = "";
                    foreach (var f in listFilms)
                    {
                        if (f.Name != film)
                        {
                            listComingFilms.Add(f);
                            film = f.Name;
                        }
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return listComingFilms;
        }
    }
}